package com.hd123.rumba.qt;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Session;
import net.sf.hibernate.Transaction;

import org.apache.log4j.Logger;

import com.hd123.rumba.exception.BusinessException;
import com.hd123.rumba.qt.def.IQueryDef;
import com.hd123.rumba.qt.def.IQueryDefinitionMgr;
import com.hd123.rumba.qt.def.ISessionValues;
import com.hd123.rumba.qt.def.QueryDefinition;
import com.hd123.rumba.qt.def.SQLOrder;
import com.hd123.rumba.qt.result.IQueryResultMgr;
import com.hd123.rumba.qt.result.QueryResultDescription;
import com.hd123.rumba.qt.run.QueryThread;
import com.hd123.rumba.runtime.HibernateSession;
import com.hd123.rumba.runtime.Manager;
import com.hd123.rumba.runtime.NamingMgr;
import com.hd123.rumba.runtime.OperateContext;
import com.hd123.rumba.sql.ResultPage;
import com.hd123.rumba.sql.SQLSubquery;
import com.hd123.rumba.user.IUser;

public class QueryMgr extends Manager implements IQueryMgr {

  public QueryResultDescription openQuery(IQueryDef queryDef, OperateContext operCtx)
      throws BusinessException, HibernateException {
    this.checkParameterNotNull(queryDef, "queryDef");
    this.checkParameterNotNull(operCtx, "operCtx");
    IQueryDefinitionMgr qdm = (IQueryDefinitionMgr) NamingMgr.getInstance().lookupMgr(
        IQueryDefinitionMgr.class);
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    QueryDefinition qd = qdm.getQueryDefinition(queryDef);
    if (qd == null)
      throw new BusinessException("查询定义文件(" + queryDef.getFilename() + ")不存在。");
    QueryResultDescription qrd = qrm.findQrd(queryDef, qd.getTime(), operCtx.getOperator());
    if (qrd == null) {
      qrd = new QueryResultDescription();
      qrd.setQdUuid(queryDef.getUuid());
      qrd.setQdName(queryDef.getRelativeFilename());
      qrd.setQdTime(qd.getTime());
      qrd.setUserUuid(operCtx.getOperator().getUuid());
      qrd.getQueryCriteria().setSg(qd.getSg());
      qrd.getDisplayCriteria().setOrders(qd.getOrders());
    }
    qrd.setQuery(qd);
    return qrd;
  }

  public QueryResultDescription findQueryResult(IQueryDef queryDef, IUser user, Hashtable params)
      throws BusinessException, HibernateException {
    IQueryDefinitionMgr qdm = (IQueryDefinitionMgr) NamingMgr.getInstance().lookupMgr(
        IQueryDefinitionMgr.class);
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);

    QueryDefinition qd;
    QueryResultDescription qrd;
    try {
      qd = qdm.getQueryDefinition(queryDef);
      QueryResultDescription qrd0 = new QueryResultDescription();
      qrd0.setQdUuid(queryDef.getUuid());
      qrd0.setQdName(queryDef.getRelativeFilename());
      qrd0.setQdTime(qd.getTime());
      qrd0.setUserUuid(user.getUuid());
      qrd0.setQuery(qd);
      qrd0.setCriteria(params);
      qrd = qrm.findQrdByQrd(qrd0);
    } catch (Exception e) {
      throw new BusinessException(e);
    }
    if (qrd != null)
      qrd.setQuery(qd);
    return qrd;
  }

  public QueryResultDescription executeQuery(IQueryDef queryDef, ISessionValues sv, Hashtable params)
      throws BusinessException, HibernateException, MaxQueryException {
    logger.debug("start executeQuery(" + queryDef.getFilename() + ","
        + sv.getCurrentUser().getLogin() + ",params)");
    IQueryDefinitionMgr qdm = (IQueryDefinitionMgr) NamingMgr.getInstance().lookupMgr(
        IQueryDefinitionMgr.class);
    QueryResultDescription qrd;
    try {
      QueryDefinition qd = qdm.getQueryDefinition(queryDef);
      qrd = new QueryResultDescription();
      qrd.setQdUuid(queryDef.getUuid());
      qrd.setQdName(queryDef.getRelativeFilename());
      qrd.setQdTime(qd.getTime());
      qrd.setUserUuid(sv.getCurrentUser().getUuid());
      qrd.setQuery(qd);
      qrd.setCriteria(params);
      executeQuery(qrd, sv);
    } catch (Exception e) {
      throw new BusinessException(e);
    }

    return qrd;
  }

  public void executeQuery(QueryResultDescription qrd, ISessionValues sv) throws MaxQueryException,
      BusinessException, HibernateException {
    logger.info("start executeQuery(" + qrd.getQdName() + ")");
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    qrd.getQuery().setSessionValues(sv);
    qrd.getQuery().setSg(qrd.getQueryCriteria().getSg());
    Session sourceSession = null, resultSession = null;
    boolean maxQuery = false;
    try {
      sourceSession = QuerySourceSession.getSessionFactory().openSession();
      try {
        sourceSession.connection(); // 尝试取得jdbc连接
      } catch (HibernateException e) {
        maxQuery = true;
        throw e;
      }
      resultSession = QueryResultSession.getSessionFactory().openSession();
      try {
        resultSession.connection(); // 尝试取得jdbc连接
      } catch (HibernateException e) {
        maxQuery = true;
        throw e;
      }
    } catch (HibernateException ex) {
      try {
        if (sourceSession != null)
          sourceSession.close();
        if (resultSession != null)
          resultSession.close();
      } catch (HibernateException e) {
        Logger.getLogger(QueryThread.class).error(e.getMessage());
      }
      if (maxQuery)
        throw new MaxQueryException(ex);
      else
        throw ex;
    }
    QueryThread thread = new QueryThread(sourceSession, resultSession, qrd);
    thread.setCurrentDomain(sv.getCurrentDomain());
    qrd.setThreadId(new Long(thread.getId()));
    qrd.setQueryThread(thread);
    qrd.setResultCount(QueryResultDescription.RESULTCOUNT_RUNNING);
    qrd.setStartTime(new java.util.Date());
    Transaction tx = HibernateSession.beginTransaction();
    try {
      qrm.saveQrd(qrd);
      HibernateSession.commit(tx);
      thread.start();
    } catch (HibernateException e) {
      HibernateSession.rollback(tx);
      throw e;
    }
    logger.info("end executeQuery(" + qrd.getQdName() + ")");
  }

  public void deleteTable(String tableName) throws BusinessException, HibernateException {
    Connection con = QueryResultSession.getSession().connection();
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.execute("DROP TABLE  " + tableName);
    } catch (SQLException e) {
      // in case table not exists
      logger.info("deleteTable(" + tableName + "):" + e.getMessage());
    } finally {
      try {
        stmt.close();
      } catch (SQLException e) {
      }
      QueryResultSession.closeSession();
    }
  }

  public void cancelQuery(String qrdUuid) throws BusinessException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    try {
      QueryResultDescription qrd = qrm.findQrdByUuid(qrdUuid);
      if (QueryResultDescription.RESULTCOUNT_RUNNING.equals(qrd.getResultCount())) {
        cancelQuery(qrd.getThreadId().longValue());
        qrd.setResultCount(QueryResultDescription.RESULTCOUNT_CANCEL);
        qrd.setTimeUsed(new Long(new Date().getTime() - qrd.getStartTime().getTime()));
      }
      ;
    } catch (HibernateException e) {
      throw new BusinessException(e);
    }
  }

  public ResultPage getResult(String base, String qrdUuid, int page) throws BusinessException,
      MaxQueryException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    IQueryDefinitionMgr qdm = (IQueryDefinitionMgr) NamingMgr.getInstance().lookupMgr(
        IQueryDefinitionMgr.class);

    try {
      QueryResultDescription qrd = qrm.findQrdByUuid(qrdUuid);
      IQueryDef queryDef = qdm.getQueryDef(qrd.getQdUuid());
      qrd.setQuery(qdm.getQueryDefinition(queryDef));
      return getResult(qrd, page);
    } catch (Exception e) {
      throw new BusinessException(e);
    }
  }

  public ResultPage getResult(String base, String qrdUuid, int start, int limit)
      throws BusinessException, MaxQueryException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    IQueryDefinitionMgr qdm = (IQueryDefinitionMgr) NamingMgr.getInstance().lookupMgr(
        IQueryDefinitionMgr.class);

    try {
      QueryResultDescription qrd = qrm.findQrdByUuid(qrdUuid);
      IQueryDef queryDef = qdm.getQueryDef(qrd.getQdUuid());
      qrd.setQuery(qdm.getQueryDefinition(queryDef));
      return getResult(qrd, start, limit);
    } catch (Exception e) {
      throw new BusinessException(e);
    }
  }

  public ResultPage getResult(QueryResultDescription qrd, int page) throws BusinessException,
      MaxQueryException {
    SQLSubquery sq = new SQLSubquery();
    sq.addSelect("*");
    sq.addFrom(qrd.getTableName());
    // 必须按照分组的列排序
    List<String> groupColumns = qrd.getQuery().getColumns().getGroupColumns();
    for (int i = 0; i < groupColumns.size(); i++) {
      String column = qrd.getQuery().getColumns().columnToResultColumn(groupColumns.get(i));
      if (column != null) {
        sq.addOrder(column);
      }
    }
    for (int i = 0; i < qrd.getDisplayCriteria().getOrders().size(); ++i) {
      SQLOrder o = (SQLOrder) qrd.getDisplayCriteria().getOrders().get(i);
      sq.addOrder(o.getColumn(), o.getOrder());
    }
    try {
      Connection conn = null;
      try {
        conn = QueryResultSession.getSession().connection();
      } catch (HibernateException e) {
        throw new MaxQueryException(e);
      }
      return ResultPage.createJdbcResultPage(conn, sq, qrd.getDisplayCriteria().getRowsPerPage()
          .intValue(), page);
    } catch (HibernateException e) {
      throw new BusinessException(e);
    }
  }

  public ResultPage getResult(QueryResultDescription qrd, int start, int limit)
      throws BusinessException, MaxQueryException {
    SQLSubquery sq = new SQLSubquery();
    sq.addSelect("*");
    sq.addFrom(qrd.getTableName());
    // 必须按照分组的列排序
    List<String> groupColumns = qrd.getQuery().getColumns().getGroupColumns();
    for (int i = 0; i < groupColumns.size(); i++) {
      String column = qrd.getQuery().getColumns().columnToResultColumn(groupColumns.get(i));
      if (column != null) {
        sq.addOrder(column);
      }
    }
    for (int i = 0; i < qrd.getDisplayCriteria().getOrders().size(); ++i) {
      SQLOrder o = (SQLOrder) qrd.getDisplayCriteria().getOrders().get(i);
      sq.addOrder(o.getColumn(), o.getOrder());
    }
    try {
      Connection conn = null;
      try {
        conn = QueryResultSession.getSession().connection();
      } catch (HibernateException e) {
        throw new MaxQueryException(e);
      }
      return ResultPage.createJdbcResultAsOnePage(conn, sq, start, limit);
    } catch (HibernateException e) {
      throw new BusinessException(e);
    }
  }

  public ResultPage getSummary(String base, String qrdUuid) throws BusinessException,
      MaxQueryException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    IQueryDefinitionMgr qdm = (IQueryDefinitionMgr) NamingMgr.getInstance().lookupMgr(
        IQueryDefinitionMgr.class);

    try {
      QueryResultDescription qrd = qrm.findQrdByUuid(qrdUuid);
      IQueryDef queryDef = qdm.getQueryDef(qrd.getQdUuid());
      qrd.setQuery(qdm.getQueryDefinition(queryDef));
      return getSummary(qrd);
    } catch (Exception e) {
      throw new BusinessException(e);
    }
  }

  public ResultPage getSummary(QueryResultDescription qrd) throws BusinessException,
      MaxQueryException {
    if (!qrd.getQuery().getColumns().hasGAggregation())
      return null;
    SQLSubquery sq = new SQLSubquery();
    sq.addSelect(qrd.getQuery().getColumns().getSummaryClause());
    sq.addFrom(qrd.getTableName());
    try {
      Connection conn = null;
      try {
        conn = QueryResultSession.getSession().connection();
      } catch (HibernateException e) {
        throw new MaxQueryException(e);
      }
      return ResultPage.createJdbcResultPage(conn, sq, qrd.getDisplayCriteria().getRowsPerPage()
          .intValue(), 0);
    } catch (HibernateException e) {
      throw new BusinessException(e);
    }
  }

  private boolean cancelQuery(long threadId) throws BusinessException {
    QueryThread thread = null;
    Thread[] list = new Thread[qtGroup.activeCount()];
    qtGroup.enumerate(list, false);
    for (int i = 0; i < list.length; ++i) {
      if (list[i].getId() == threadId) {
        thread = (QueryThread) list[i];
        break;
      }
    }
    try {
      if (thread != null) {
        thread.cancel();
        thread.join();
      } else
        return false;
    } catch (SQLException e) {
      throw new BusinessException(e);
    } catch (InterruptedException e) {
    }
    return true;
  }

  public QueryThread findQueryThread(QueryResultDescription qrd) {
    QueryThread thread = null;
    if (qrd.getThreadId() != null) {
      Thread[] list = new Thread[qtGroup.activeCount()];
      qtGroup.enumerate(list, false);
      for (int i = 0; i < list.length; ++i) {
        if (list[i].getId() == qrd.getThreadId().longValue()) {
          thread = (QueryThread) list[i];
          break;
        }
      }
    }
    return thread;
  }

  /**
   * 定时执行这个方法, 清除过时的或过多的查询结果(qrd和结果表). 参数设置在rumba.properties文件中. <br>
   * 默认值: 查询结果保留天数=7,每个用户保留的最多查询结果数=50
   */
  public void removeExpiredQueryResults() throws BusinessException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    try {
      Statement stm = QueryResultSession.getSession().connection().createStatement();
      try {
        List<QueryResultDescription> qrds = qrm.findExpiredResults();
        for (int i = 0; i < qrds.size(); ++i) {
          QueryResultDescription qrd = qrds.get(i);
          removeQrd(qrd, stm);
        }
      } finally {
        stm.close();
        QueryResultSession.closeSession();
      }
    } catch (Exception e) {
      logger.error("清除查询结果错误: " + e.getMessage());
    }
  }

  private void removeQrd(QueryResultDescription qrd, Statement stm) throws BusinessException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    try {
      try {
        stm.execute("drop table " + qrd.getTableName());
      } catch (SQLException e) {
        // 应该只可以忽略"表不存在"的错误, 但是对不同的数据库类型, e的内容不一样. 怎么解决?
      }
      Transaction tx = null;
      try {
        tx = HibernateSession.beginTransaction();
        qrm.removeQrd(qrd);
        HibernateSession.commit(tx);
      } catch (HibernateException e) {
        if (tx != null)
          try {
            HibernateSession.rollback(tx);
          } catch (HibernateException ex) {
          }
      }
    } catch (Exception e) {
      logger.error("清除查询结果错误: qrd.uuid=" + qrd.getUuid() + " error=" + e.getMessage());
    }
  }

  private static ThreadGroup qtGroup = null;

  private static Logger logger = Logger.getLogger(QueryMgr.class);

  public QueryMgr() {
    super();
    qtGroup = new ThreadGroup("qt");
  }

  public ThreadGroup getQtGroup() {
    return qtGroup;
  }

}
